package com.tpc.shuyu.utils;


import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.tomcat.util.buf.StringUtils;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;


@Slf4j
public class TableSaveUtil {

    public static void tableSave(Connection connection, String tableName, JSONArray jsonArray) {
        jsonArray = JSONUtil.transToArray(jsonArray.toString());
        if(jsonArray.isEmpty()){
            return;
        }
        Map<String, String> tableColumnTypeMap = new HashMap<>();
        Object[][] objects = null;
        Object[][] objectDel = null;
        String sqlInsert = "";
        String sqlDelete = "";
        QueryRunner queryRunner = new QueryRunner();
        try {
            tableColumnTypeMap = JDBCUtil.getTableColumnType(connection, tableName);
            String pkColumnName = StringUtils.join(JDBCUtil.getTableColumnPK(connection,tableName), ',').toLowerCase();
            if(!pkColumnName.isEmpty()){
                String[] columnNameArray = pkColumnName.split(",");
                objectDel = new Object[jsonArray.size()][columnNameArray.length];

                sqlDelete = "DELETE FROM  " + tableName.toUpperCase() + " WHERE " ;

                for(int i = 0; i <columnNameArray.length;i++){
                    sqlDelete = sqlDelete + columnNameArray[i].toUpperCase() + " = ? AND " ;
                }
                sqlDelete = sqlDelete.substring(0,sqlDelete.length()-4) ;
                log.info("删除语句：" + sqlDelete);
                for( int j = 0;j<jsonArray.size();j++){
                    JSONObject jsonObject = jsonArray.getJSONObject(j);
                    String columns = " ";
                    for(int k = 0 ;k < columnNameArray.length;k++){
                        Object obj = null ;
                        if(tableColumnTypeMap.get(columnNameArray[k]).equals("int")){
                            columns = columns +  (jsonObject.getString(columnNameArray[k]) == null ? 0 : jsonObject.getString(columnNameArray[k]) ) + " AS " + columnNameArray[k].toUpperCase() + "," ;
                            int columnsValue = 0 ;
                            if (jsonObject.getBigDecimal(columnNameArray[k]) != null){
                                columnsValue = jsonObject.getIntValue(columnNameArray[k]);
                            }else{
                                throw new RuntimeException("【"+columnNameArray[k].toUpperCase() + "】主键字段不允许为空");
                            }
                            obj = columnsValue ;
                        } else if (tableColumnTypeMap.get(columnNameArray[k]).equals("decimal")){
                            columns = columns +  (jsonObject.getString(columnNameArray[k]) == null ? 0 : jsonObject.getString(columnNameArray[k]) ) + " AS " + columnNameArray[k].toUpperCase() + "," ;
                            BigDecimal bigDecimal = new BigDecimal(0);
                            if (jsonObject.getBigDecimal(columnNameArray[k]) != null){
                                bigDecimal = jsonObject.getBigDecimal(columnNameArray[k]);
                            }else{
                                throw new RuntimeException("【"+columnNameArray[k].toUpperCase() + "】主键字段不允许为空");
                            }
                            obj = bigDecimal ;
                        } else if (tableColumnTypeMap.get(columnNameArray[k]).equals("number")){
                            columns = columns +  (jsonObject.getString(columnNameArray[k]) == null ? 0 : jsonObject.getString(columnNameArray[k]) ) + " AS " + columnNameArray[k].toUpperCase() + "," ;
                            BigDecimal bigDecimal = new BigDecimal(0);
                            if (jsonObject.getBigDecimal(columnNameArray[k]) != null){
                                bigDecimal = jsonObject.getBigDecimal(columnNameArray[k]);
                            }else{
                                throw new RuntimeException("【"+columnNameArray[k].toUpperCase() + "】主键字段不允许为空");
                            }
                            obj = bigDecimal ;
                        } else {
                            if(jsonObject.getString(columnNameArray[k])!=null) {
                                columns = columns + " '" + (jsonObject.getString(columnNameArray[k]) == null ? 0 : jsonObject.getString(columnNameArray[k])) + "' AS " + columnNameArray[k].toUpperCase() + ",";
                            }else{
                                throw new RuntimeException( "【"+columnNameArray[k].toUpperCase() + "】主键字段不允许为空");
                            }
                            obj = jsonObject.getString(columnNameArray[k]) == null ? "" : jsonObject.getString(columnNameArray[k]);
                        }
                        objectDel[j][k] = obj;
                    }
                    log.info("删除参数："+ columns.substring(0 , columns.length() -1 )  );
                    System.out.println(columns.substring(0 , columns.length() -1 ));
                }
            }
            String columnName = StringUtils.join(tableColumnTypeMap.keySet(), ',');
            log.info("columnName:"+columnName);
            if(!columnName.isEmpty()){
                String[] columnNameArray = columnName.split(",");
                objects = new Object[jsonArray.size()][columnNameArray.length];
                sqlInsert = "INSERT INTO " + tableName.toUpperCase() + " ( " + columnName.toUpperCase() + " ) values ( " ;
                for(int i = 0;i<columnNameArray.length;i++){
                    sqlInsert = sqlInsert + " ?, ";
                }
                sqlInsert = sqlInsert.substring(0,sqlInsert.length()-2) + ")";
                log.info("插入语句：" + sqlInsert);
                for( int j = 0;j<jsonArray.size();j++){
                    JSONObject jsonObject = jsonArray.getJSONObject(j);
                    String columns = "SELECT ";
                    for(int k = 0 ;k < columnNameArray.length;k++){
                        Object obj = null ;
                        try{
                            if(tableColumnTypeMap.get(columnNameArray[k]).equals("int")){
                                columns = columns +  (jsonObject.getString(columnNameArray[k]) == null ? 0 : jsonObject.getString(columnNameArray[k]) ) + " AS " + columnNameArray[k].toUpperCase() + "," ;
                                int columnsValue = 0 ;
                                if (jsonObject.getInteger(columnNameArray[k]) != null){
                                    columnsValue = jsonObject.getIntValue(columnNameArray[k]);
                                }
                                obj = columnsValue ;
                            } else if (tableColumnTypeMap.get(columnNameArray[k]).equals("decimal")){
                                columns = columns +  (jsonObject.getString(columnNameArray[k]) == null ? 0 : jsonObject.getString(columnNameArray[k]) ) + " AS " + columnNameArray[k].toUpperCase() + "," ;
                                BigDecimal bigDecimal = new BigDecimal(0);
                                if (jsonObject.getBigDecimal(columnNameArray[k]) != null){
                                    bigDecimal = jsonObject.getBigDecimal(columnNameArray[k]);
                                }
                                obj = bigDecimal ;
                            } else if (tableColumnTypeMap.get(columnNameArray[k]).equals("number")){
                                columns = columns +  (jsonObject.getString(columnNameArray[k]) == null ? 0 : jsonObject.getString(columnNameArray[k]) ) + " AS " + columnNameArray[k].toUpperCase() + "," ;
                                BigDecimal bigDecimal = new BigDecimal(0);
                                if (jsonObject.getBigDecimal(columnNameArray[k]) != null){
                                    bigDecimal = jsonObject.getBigDecimal(columnNameArray[k]);
                                }
                                obj = bigDecimal ;
                            } else {
                                columns = columns + " '" + (jsonObject.getString(columnNameArray[k]) == null ? "" : jsonObject.getString(columnNameArray[k]) ) + "' AS " + columnNameArray[k].toUpperCase() + "," ;
                                obj = jsonObject.getString(columnNameArray[k]) == null ? "" : jsonObject.getString(columnNameArray[k]);
                            }
                        }catch (Exception e){
                            throw new RuntimeException(e);
                        }
                        objects[j][k] = obj;
                    }
                    log.info("插入参数："+ columns.substring(0 , columns.length() -1 ) );
                }
                if(!pkColumnName.isEmpty()){
                    queryRunner.batch(connection,sqlDelete,objectDel);
                }
                queryRunner.batch(connection,sqlInsert,objects);
            } else {
                throw new RuntimeException(tableName + "表和视图不存在！ ");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
